package test;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

public class TeacherDaoJDBCImpl implements TeacherDao{

	@Override
	public boolean writeStudent(Student student) {
		Connection conn = null;
		PreparedStatement pstat = null;
		String sql = "insert into student(name,grade,gender,birthday) values(?,?,?,?) ";
		int result = -1;
		
		try {
			conn = JDBCUtil.getConnection();
			pstat = conn.prepareStatement(sql);
			pstat.setString(1, student.getName());
			pstat.setString(2, student.getGrade());
			pstat.setString(3, student.getGender());
			pstat.setString(4, student.getBirthday());
			result = pstat.executeUpdate();
			
		}catch (SQLException sqle) {
			sqle.printStackTrace();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			JDBCUtil.realeaseAll(null,pstat, conn);
		}
		return result>0?true:false;
	}

	@Override
	public boolean insertJavaScore(int id, double javaScore) {
		Connection conn = null;
		PreparedStatement pstat = null;
		String sql="update student set java="+javaScore+"where id ="+id+";" ;
		int result = -1;
		
		try {
			conn = JDBCUtil.getConnection();
			pstat = conn.prepareStatement(sql);
			result = pstat.executeUpdate();
			
		}catch (SQLException sqle) {
			sqle.printStackTrace();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			JDBCUtil.realeaseAll(null,pstat, conn);
		}
		return result>0?true:false;
	}
	public boolean insertMathScore(int id, double mathScore) {
		Connection conn = null;
		PreparedStatement pstat = null;
		String sql="update student set math="+mathScore+"where id ="+id+";" ;
		int result = -1;
		
		try {
			conn = JDBCUtil.getConnection();
			pstat = conn.prepareStatement(sql);
			result = pstat.executeUpdate();
			
		}catch (SQLException sqle) {
			sqle.printStackTrace();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			JDBCUtil.realeaseAll(null,pstat, conn);
		}
		return result>0?true:false;
	}
	public boolean insertSportsScore(int id, double sportsScore) {
		Connection conn = null;
		PreparedStatement pstat = null;
		String sql="update student set sports="+sportsScore+"where id ="+id+";" ;
		int result = -1;
		
		try {
			conn = JDBCUtil.getConnection();
			pstat = conn.prepareStatement(sql);
			result = pstat.executeUpdate();
			
		}catch (SQLException sqle) {
			sqle.printStackTrace();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			JDBCUtil.realeaseAll(null,pstat, conn);
		}
		return result>0?true:false;
	}

	@Override
	public Student searchStudent(int id) {
		Connection conn = null;
		Statement state = null;
		String sql="select * from student where id="+id+";";
		ResultSet result;
		Student student = null;
		ArrayList<Subject> subjectList=new ArrayList<>();
		int flag=0;
		try {
			conn = JDBCUtil.getConnection();
			state = conn.createStatement();
			result = state.executeQuery(sql);
			while (result.next()) {
				flag=1;
				Subject subject1=new Subject("java",result.getDouble("java"),null);
				Subject subject2=new Subject("math",result.getDouble("math"),null);
				Subject subject3=new Subject("sports",result.getDouble("sports"),null);
				subjectList.add(subject1);
				subjectList.add(subject2);
				subjectList.add(subject3);
				student=new Student(id,result.getString("name"),result.getString("grade"),result.getString("gender"),result.getString("birthday"),subjectList);
			}
			if(flag==0) {
				System.out.println("学号不存在");
			}
			
		}catch (SQLException sqle) {
			sqle.printStackTrace();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			JDBCUtil.realeaseAll(null,state, conn);
		}
		return student;
	}

	@Override
	public ArrayList<Student> displayAll() {
		Connection conn = null;
		Statement state = null;
		String sql="select * from student;";
		ResultSet result;
		ArrayList<Student> studentList = new ArrayList<>();
		try {
			conn = JDBCUtil.getConnection();
			state = conn.createStatement();
			result = state.executeQuery(sql);
			while (result.next()) {
				ArrayList<Subject> subjectList=new ArrayList<>();
				Subject subject1=new Subject("java",result.getDouble("java"),null);
				Subject subject2=new Subject("math",result.getDouble("math"),null);
				Subject subject3=new Subject("sports",result.getDouble("sports"),null);
				subjectList.add(subject1);
				subjectList.add(subject2);
				subjectList.add(subject3);
				Student student=new Student(result.getInt("id"),result.getString("name"),result.getString("grade"),result.getString("gender"),result.getString("birthday"),subjectList);
				studentList.add(student);
			}
			
		}catch (SQLException sqle) {
			sqle.printStackTrace();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			JDBCUtil.realeaseAll(null,state, conn);
		}
		return studentList;
	}
	public ArrayList<Student> displayByGrade(String grade){
		Connection conn = null;
		Statement state = null;
		String sql="select * from student where grade="+grade+";";
		ResultSet result;
		int flag=0;
		ArrayList<Student> studentList = new ArrayList<>();
		try {
			conn = JDBCUtil.getConnection();
			state = conn.createStatement();
			result = state.executeQuery(sql);
			while (result.next()) {
				flag=1;
				ArrayList<Subject> subjectList=new ArrayList<>();
				Subject subject1=new Subject("java",result.getDouble("java"),null);
				Subject subject2=new Subject("math",result.getDouble("math"),null);
				Subject subject3=new Subject("sports",result.getDouble("sports"),null);
				subjectList.add(subject1);
				subjectList.add(subject2);
				subjectList.add(subject3);
				Student student=new Student(result.getInt("id"),result.getString("name"),result.getString("grade"),result.getString("gender"),result.getString("birthday"),subjectList);
				studentList.add(student);
			}
			if(flag==0) {
				System.out.println("班级不存在");
			}
			
		}catch (SQLException sqle) {
			sqle.printStackTrace();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			JDBCUtil.realeaseAll(null,state, conn);
		}
		return studentList;
	}
}
